package edu.jhu.cs.oose.scheduleit.dao.impl;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.HibernateTemplate;

import edu.jhu.cs.oose.scheduleit.dao.GroupDao;
import edu.jhu.cs.oose.scheduleit.util.Filter;
import edu.jhu.cs.oose.scheduleit.util.FilterParser;
import edu.jhu.cs.oose.scheduleit.util.JoinStatus;
import edu.jhu.cs.oose.scheduleit.vo.Group;
import edu.jhu.cs.oose.scheduleit.vo.User;
import edu.jhu.cs.oose.scheduleit.vo.UserInfo;
/**
 * The implementation class of the GroupDao interface
 * @author oosegroup2
 *
 */
public class GroupDaoImpl implements GroupDao{

	private HibernateTemplate hibernateTemplate;

	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public int countUser(Group group, JoinStatus status) {
		String attribute = null;
		switch (status){
			case JOINED:
				attribute ="joinedUsers";
				break;
			case APPLIED:
				attribute ="appliedUsers";
				break;
			case DENIED:
				attribute = "deniedUsers";
				break;
		}
		StringBuilder queryString = new StringBuilder();
		queryString.append("SELECT COUNT(u) FROM Group as g inner join g.");
		queryString.append(attribute);
		queryString.append(" as u WHERE g.id = ?");
		List result = this.hibernateTemplate.find(queryString.toString(),new Object[]{group.getId()});
		return Integer.parseInt(result.get(0).toString());
		
	}

	@Override
	public void deleteGroup(Group group) {
		this.hibernateTemplate.delete(group);
	}

	

	@SuppressWarnings("unchecked")
	@Override
	public List<Group> getGroup(List<Filter> filters, int offset, int limit) {
		return this.hibernateTemplate.findByCriteria(FilterParser.parse(Group.class, filters), offset, limit);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public int countGroup(List<Filter> filters) {
		DetachedCriteria criteria = FilterParser.parse(Group.class, filters);
		criteria.setProjection(Projections.count("id"));
		List result = this.hibernateTemplate.findByCriteria(criteria);
		return Integer.parseInt(result.get(0).toString());
	}
	

	
	@SuppressWarnings("unchecked")
	@Override
	public Group getGroup(int groupID) {
		List<Group> result = this.hibernateTemplate.find(" from Group Where id ="+groupID);
		return result.size() == 0? null:result.get(0);		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserInfo> getUser(Group group, JoinStatus status, int offset,
			int limit) {
		String attribute = null;
		switch (status){
			case JOINED:
				attribute ="joinedUsers";
				break;
			case APPLIED:
				attribute ="appliedUsers";
				break;
			case DENIED:
				attribute = "deniedUsers";
				break;
				
		}
		StringBuilder queryString = new StringBuilder("SELECT u FROM Group as g inner join g.");
		queryString.append(attribute);
		queryString.append(" as u WHERE g.id = ");
		queryString.append(group.getId());
		queryString.append(" order by u.id");
		
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			
			Query hqlQuery = session.createQuery(queryString.toString());
			hqlQuery.setFirstResult(offset);
			if (limit <=0){
				hqlQuery.setMaxResults(Integer.MAX_VALUE);
			}else{
				hqlQuery.setMaxResults(limit);
			}
			return (List<UserInfo>) hqlQuery.list();	
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}

	@Override
	public void addUser(Group group, User user, JoinStatus status){	
		String teamTable = null;
		String teamColumn = null;
		StringBuilder queryString = null;
		switch(status){
			case JOINED:
				teamTable = "Team_joinedUsers";
				teamColumn = "joinedUsers_id";
				break;
			case APPLIED:
				teamTable = "Team_appliedUsers";
				teamColumn = "appliedUsers_id";
				break;
			case DENIED:
				teamTable =	"Team_deniedUsers";
				teamColumn = "deniedUsers_id";
				break;
		}
				
		queryString = new StringBuilder("INSERT INTO ");
		queryString.append(teamTable);
		queryString.append(" (Team_id,");
		queryString.append(teamColumn);
		queryString.append(") values(");
		queryString.append(group.getId());
		queryString.append(",");
		queryString.append(user.getId());
		queryString.append(")");
		
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.executeUpdate();				
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}

	@Override
	public void deleteUser(Group group, User user, JoinStatus status) {
		String teamTable = null;
		String teamColumn = null;
		StringBuilder queryString = null;
		switch(status){
			case JOINED:
				teamTable = "Team_joinedUsers";
				teamColumn = "joinedUsers_id";
				break;
			case APPLIED:
				teamTable = "Team_appliedUsers";
				teamColumn = "appliedUsers_id";
				break;
			case DENIED:
				teamTable =	"Team_deniedUsers";
				teamColumn = "deniedUsers_id";
				break;
		}
		
		queryString = new StringBuilder("DELETE FROM ");
		queryString.append(teamTable);
		queryString.append(" where ");
		queryString.append(" Team_id = ");
		queryString.append(group.getId());
		queryString.append(" and ");
		queryString.append(teamColumn);
		queryString.append(" = ");
		queryString.append(user.getId());
		
		Session session = null;
		try {
			session = this.hibernateTemplate.getSessionFactory().openSession();
			SQLQuery sqlQuery = session.createSQLQuery(queryString.toString());
			sqlQuery.executeUpdate();		
		} catch (HibernateException e) {
			throw e;
		} finally{
			queryString.setLength(0);
			if (session != null && session.isOpen()){
				session.close();
			}
		}		
	}
	


	@Override
	public void save(Group group) {
		this.hibernateTemplate.saveOrUpdate(group);
	}


	@SuppressWarnings("unchecked")
	@Override
	public boolean isJoined(Group group, User user) {
		String queryString= "SELECT u FROM Group as g inner join g.joinedUsers as u WHERE g.id = ? and u.id = ?";
		Session session = null;
		try {
			List<UserInfo> result = this.hibernateTemplate.find(queryString, new Object[]{group.getId(), user.getId()});
			return (result.size()>0);
		} catch (HibernateException e) {
			throw e;
		} finally{
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}


	@SuppressWarnings("unchecked")
	@Override
	public boolean isApplied(Group group, User user) {
		String queryString= "SELECT u FROM Group as g inner join g.appliedUsers as u WHERE g.id = ? and u.id = ?";
		Session session = null;
		try {
			List<UserInfo> result = this.hibernateTemplate.find(queryString, new Object[]{group.getId(), user.getId()});
			return (result.size()>0);
		} catch (HibernateException e) {
			throw e;
		} finally{
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}


	@SuppressWarnings("unchecked")
	@Override	
	public boolean isDenied(Group group, User user) {
		String queryString= "SELECT u FROM Group as g inner join g.deniedUsers as u WHERE g.id = ? and u.id = ?";
																 
		Session session = null;
		try {
			List<UserInfo> result = this.hibernateTemplate.find(queryString, new Object[]{group.getId(), user.getId()});
			return (result.size()>0);
		} catch (HibernateException e) {
			throw e;
		} finally{
			if (session != null && session.isOpen()){
				session.close();
			}
		}
	}


	
	

}
